
DECLARE @MONTH1 DATE=DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,(SELECT MAX_DATE_ENDMONTH FROM DATE_EM_REPORT))-10,0))
PRINT @MONTH1
DECLARE @COUNT INT =1
DECLARE @FULLDATE NVARCHAR(8)
DELETE FROM TOI_LAST_12_MONTH
WHILE @COUNT <13
BEGIN
	DECLARE @MONTH NVARCHAR(6) = CONVERT(NVARCHAR(6), @MONTH1,112)
	IF (@MONTH1> '20160701' and @MONTH1 < '20170101')
	BEGIN
	EXEC('
IF EXISTS
(SELECT *
FROM DBO.SYSOBJECTS O
WHERE O.XTYPE IN (''U'') AND O.NAME = ''TOI_RAW'+@MONTH+''')
DROP TABLE TOI_RAW'+@MONTH+'

SELECT A.CIF,B.DAO,A.AMT AS TOI 
INTO TOI_RAW'+@MONTH+'
FROM TRANGDTT18.SME2016.DBO.NFI_TOI_'+@MONTH+' A, TBL_CUSTOMER B
WHERE A.TYPE=''TOI'' AND A.CIF = B.CIF
')
	END
	IF (@MONTH1> '20170101')
	BEGIN
	EXEC('
IF EXISTS
(SELECT *
FROM DBO.SYSOBJECTS O
WHERE O.XTYPE IN (''U'') AND O.NAME = ''TOI_RAW'+@MONTH+''')
DROP TABLE TOI_RAW'+@MONTH+'

SELECT A.CIF,B.DAO,A.AMT AS TOI 
INTO TOI_RAW'+@MONTH+'
FROM TRANGDTT18.SME2017.DBO.NFI_TOI_'+@MONTH+' A, TBL_CUSTOMER B
WHERE A.TYPE=''TOI'' AND A.CIF = B.CIF
')
	END

	IF (@MONTH1< '20160701')
	BEGIN
	EXEC('
IF EXISTS
(SELECT *
FROM DBO.SYSOBJECTS O
WHERE O.XTYPE IN (''U'') AND O.NAME = ''TOI_RAW'+@MONTH+''')
DROP TABLE TOI_RAW'+@MONTH+'

SELECT A.CIF,B.DAO,A.AMT AS TOI 
INTO TOI_RAW'+@MONTH+'
FROM TRANGDTT18.SME2016_GIANGPT3.DBO.NFI_TOI_'+@MONTH+' A, TBL_CUSTOMER B
WHERE A.TYPE=''TOI'' AND A.CIF = B.CIF
')
	END
PRINT(@MONTH)
EXEC('
INSERT TOI_LAST_12_MONTH
SELECT ''MONTH'+@COUNT+''' AS MONTH,CIF,DAO,SUM(TOI) AS TOI
FROM TOI_RAW'+@MONTH+' WHERE CIF IS NOT NULL AND DAO IS NOT NULL
GROUP BY CIF, DAO ')


	--LAST DAY OF ANY MONTH AND YEAR
	SET @MONTH1 = DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,@MONTH1)+2,0))
--PRINT @MONTH1
SET @COUNT = @COUNT + 1
END

--SELECT * FROM TOI_201603 WHERE CIF = '1003506'


--SELECT * FROM TOI_LAST_12_MONTH

--DELETE FROM TOI_LAST_12_MONTH

--SELECT * FROM TOI_201609

--UPDATE A
--SET A.DAO_CIF = B.DAO
----SELECT A.*, B.DAO
--FROM TOI_LAST_12_MONTH A, TBL_CUSTOMER B
--WHERE A.CIF = B.CIF 
DELETE FROM TBL_TOI_ACCUMULATED

INSERT TBL_TOI_ACCUMULATED 
SELECT (SELECT MAX_DATE_ENDMONTH FROM DATE_EM_REPORT) AS MONTH,*
FROM 
(SELECT CIF,DAO_CIF,TOI,MONTH
FROM TOI_LAST_12_MONTH ) AS A
PIVOT
(MAX(TOI) FOR MONTH IN (MONTH1,MONTH2,MONTH3,MONTH4,MONTH5,MONTH6,MONTH7,MONTH8,MONTH9,MONTH10,MONTH11,MONTH12)) AS PVT
WHERE CIF IN (SELECT CIF FROM TBL_CUSTOMER)

--DELETE FROM TBL_TOI_ACCUMULATED
--SELECT * FROM TBL_TOI_ACCUMULATED ORDER BY CIF


--SELECT * FROM TOI_LAST_12_MONTH WHERE CIF = '1003506' ORDER BY MONTH
--UPDATE TBL_TOI_ACCUMULATED
--SET MONTH = NULL

  --DECLARE @COUNT INT = 1
  SET @COUNT = 1
  WHILE (@COUNT <13)
  BEGIN 
  EXEC('
  UPDATE TBL_TOI_ACCUMULATED SET MONTH'+@COUNT+'=0 WHERE MONTH'+@COUNT+' IS NULL
  ')
  SET @COUNT = @COUNT +1
  END